﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using Model.Security.FindEntityInfo;
using DBUtility;
using System.Data.SqlClient;
using MyExection;
using Model.Security.EntityInfo;
using Util;

namespace SecurityDAL
{
    /// <summary>
    /// 功能介绍：部门数据访问层
    /// </summary>
    public class DeptDAL
    {
        private readonly string ConnStr = ConfigurationManager.AppSettings["ERPDataConnStr"];

        public DeptDAL()
        {
        }
        /// <summary>
        /// 根据实体获取部门信息
        /// </summary>
        /// <param name="findEntity"></param>
        /// <returns></returns>
        public DataTable SearchByEntity(FindDept findEntity)
        {
            DataTable searchDT = new DataTable();
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT *  ");
            sbSql.Append(" FROM TB_BuMen ");
            sbSql.Append(" where BM_MingCheng like @BM_MingCheng and BM_ShiFouYouXiao=@BM_ShiFouYouXiao order by BM_MingCheng");
            try
            {
                searchDT = DBSQLHelper.GetDataTable(ConnStr, CommandType.Text, sbSql.ToString(), GetSearchByEntityParas(findEntity));
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "DeptDAL.SearchByEntity(FindDept findEntity)", "根据实体获取部门信息");
            }
            return searchDT;
        }
        private SqlParameter[] GetSearchByEntityParas(FindDept findEntity)
        {
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@BM_MingCheng",'%'+findEntity.BM_MingCheng+'%'),
                new SqlParameter("@BM_ShiFouYouXiao",findEntity.BM_ShiFouYouXiao)
            };
            return parameter;
        }
        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetBuMen()
        {
            DataTable searchDT = new DataTable();
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT *  ");
            sbSql.Append(" FROM TB_BuMen ");
            sbSql.Append(" where BM_ShiFouYouXiao=1 order by BM_MingCheng");
            try
            {
                searchDT = DBSQLHelper.GetDataTable(ConnStr, CommandType.Text, sbSql.ToString(), null);
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "DeptDAL.GetBuMen()", "获取部门信息");
            }
            return searchDT;
        }
        /// <summary>
        /// 根据部门id获取部门详细信息
        /// </summary>
        /// <param name="BM_ID"></param>
        /// <returns></returns>
        public Dept GetListDetailById(int BM_ID)
        {
            DataRow row = null;
            Dept buMenEntity = new Dept();
            StringBuilder sbSqlBuilder = new StringBuilder();
            try
            {
                sbSqlBuilder.Append("SELECT * FROM TB_BuMen WHERE TB_BuMen.BM_ID = @BM_ID");
                string sqlStr = sbSqlBuilder.ToString();
                SqlParameter[] parameters = new SqlParameter[]{
                        new SqlParameter("@BM_ID",BM_ID)
                    };
                DataTable tempDT = new DataTable();
                tempDT = DBSQLHelper.GetDataTable(ConnStr, CommandType.Text, sqlStr, parameters);
                if ((tempDT != null) && (tempDT.Rows.Count > 0))
                {
                    row = tempDT.Rows[0];
                }
                else
                {
                    return buMenEntity;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "DeptDAL.GetListDetailById(string key)", "根据部门id获取部门详细信息");
            }
            buMenEntity.BM_MingCheng = DataSwitch.ConvertObjectToString(row["BM_MingCheng"]);
            buMenEntity.BM_ID = DataSwitch.ConvertObjectToInt32(row["BM_ID"]);
            buMenEntity.BM_SourceID = DataSwitch.ConvertObjectToString(row["BM_SourceID"]);
            if (DataSwitch.ConvertObjectToBoolean(row["BM_ShiFouYouXiao"]))
            {
                buMenEntity.BM_ShiFouYouXiao = 1;
            }
            else
            {
                buMenEntity.BM_ShiFouYouXiao = 0;
            }
            return buMenEntity;
        }
        /// <summary>
        /// 添加部门
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool InsertDept(Dept entityInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO TB_BuMen( BM_MingCheng,BM_ShiFouYouXiao,BM_SourceID) values( @BM_MingCheng,@BM_ShiFouYouXiao,@BM_SourceID)");
            try
            {
                int reslut = DBSQLHelper.ExecuteNonQuery(ConnStr, CommandType.Text, strSql.ToString(), GetInsertDeptParas(entityInfo));
                if (reslut > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "DeptDAL.InsertDept()", "获取部门信息");
            }
        }
        private SqlParameter[] GetInsertDeptParas(Dept entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    //new SqlParameter("@BM_ID",entity.BM_ID),
                    new SqlParameter("@BM_MingCheng",entity.BM_MingCheng),
                    new SqlParameter("@BM_ShiFouYouXiao",entity.BM_ShiFouYouXiao),
                    new SqlParameter("@BM_SourceID",entity.BM_SourceID)
                 };
            return parameter;
        }
        /// <summary>
        /// 更新部门信息
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool UpdateDept(Dept entityInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TB_BuMen SET  BM_MingCheng=@BM_MingCheng,BM_ShiFouYouXiao=@BM_ShiFouYouXiao,BM_ID=@BM_ID WHERE  1 = 1 AND BM_ID= @BM_ID");
            try
            {
                int reslut = DBSQLHelper.ExecuteNonQuery(ConnStr, CommandType.Text, strSql.ToString(), GetUpdateDeptParas(entityInfo));
                if (reslut > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "DeptDAL.UpdateDept()", "更新部门信息");
            }
        }
        private SqlParameter[] GetUpdateDeptParas(Dept entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@BM_ID",entity.BM_ID),
                    new SqlParameter("@BM_MingCheng",entity.BM_MingCheng),
                    new SqlParameter("@BM_ShiFouYouXiao",entity.BM_ShiFouYouXiao),
                    new SqlParameter("@BM_ID",entity.BM_ID)
                 };
            return parameter;
        }
        /// <summary>
        /// 删除部门信息
        /// </summary>
        /// <param name="BM_ID"></param>
        /// <returns></returns>
        public bool DeleteDept(int BM_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE TB_BuMen WHERE BM_ID = @BM_ID");
            try
            {
                int reslut = DBSQLHelper.ExecuteNonQuery(ConnStr, CommandType.Text, strSql.ToString(), new SqlParameter[] { new SqlParameter("@BM_ID", BM_ID) });
                if (reslut > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "DeptDAL.DeleteDept(int BM_ID)", "删除部门信息");
            }
        }


        public DataTable GetDeptFromProc()
        {

            SqlCommand myCommand = new SqlCommand();

            //   指定要执行的存储过程名称 
            myCommand.CommandText = "proGetDept";
            DataSet myDS = new DataSet();
            DataTable DeptTable = new DataTable();
            List<FindDept> deptList = new List<FindDept>();
            myDS = DBSQLHelper.GetDataSet(CommandType.StoredProcedure, myCommand.CommandText, null);
            DeptTable = myDS.Tables[0];

            return DeptTable;
        }
    }
}
